import psycopg2
import logging
from decimal import Decimal

# 配置日志
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

def check_course_data():
    try:
        # 连接数据库
        conn = psycopg2.connect(
            host='192.168.186.126',
            port=5432,
            database='xaunke_database',
            user='cywq',
            password='1234567890'
        )
        
        with conn.cursor() as cur:
            # 1. 查询C017课程的评价数据
            cur.execute("""
                SELECT 
                    c."课程编号",
                    c."课程名称",
                    COUNT(*) as "评价数量",
                    ROUND(AVG("得分高低（1到10）")::numeric, 2) as "平均得分高低",
                    ROUND(AVG("水课程度（1到10）")::numeric, 2) as "平均水课程度"
                FROM "课程表" c
                LEFT JOIN "课程评价表" cr ON c."课程编号" = cr."课程编号"
                WHERE c."课程编号" = 'C017'
                GROUP BY c."课程编号", c."课程名称"
            """)
            
            course_summary = cur.fetchone()
            logger.info("\nC017课程汇总信息:")
            if course_summary:
                logger.info(f"课程编号: {course_summary[0]}")
                logger.info(f"课程名称: {course_summary[1]}")
                logger.info(f"评价数量: {course_summary[2]}")
                logger.info(f"平均得分高低: {course_summary[3]}")
                logger.info(f"平均水课程度: {course_summary[4]}")
            else:
                logger.info("未找到课程")
            
            # 2. 查看具体评价记录
            cur.execute("""
                SELECT *
                FROM "课程评价表"
                WHERE "课程编号" = 'C017'
                ORDER BY "评价编号"
            """)
            
            ratings = cur.fetchall()
            logger.info("\nC017课程的具体评价记录:")
            if ratings:
                columns = [desc[0] for desc in cur.description]
                for rating in ratings:
                    logger.info(dict(zip(columns, rating)))
            else:
                logger.info("没有找到评价记录")

            # 3. 检查API返回的数据格式
            cur.execute("""
                WITH course_ratings AS (
                    SELECT 
                        "课程编号",
                        ROUND(AVG("得分高低（1到10）")::numeric, 2) as "得分高低",
                        ROUND(AVG("水课程度（1到10）")::numeric, 2) as "水课程度",
                        COUNT(*) as rating_count
                    FROM "课程评价表"
                    WHERE "课程编号" = 'C017'
                    GROUP BY "课程编号"
                )
                SELECT 
                    c."课程编号",
                    c."课程名称",
                    c."学分",
                    json_agg(DISTINCT jsonb_build_object(
                        '教室', t."教室"
                    )) FILTER (WHERE t."教室" IS NOT NULL) as "教室信息",
                    cr."得分高低",
                    cr."水课程度",
                    cr.rating_count as "评价数量"
                FROM "课程表" c
                LEFT JOIN "授课表" t ON c."课程编号" = t."课程编号"
                LEFT JOIN course_ratings cr ON c."课程编号" = cr."课程编号"
                WHERE c."课程编号" = 'C017'
                GROUP BY 
                    c."课程编号", 
                    c."课程名称", 
                    c."学分",
                    cr."得分高低",
                    cr."水课程度",
                    cr.rating_count
            """)
            
            api_data = cur.fetchone()
            logger.info("\nAPI返回的数据格式:")
            if api_data:
                columns = ["课程编号", "课程名称", "学分", "教室信息", "得分高低", "水课程度", "评价数量"]
                logger.info(dict(zip(columns, api_data)))
            else:
                logger.info("API查询未返回数据")
        
        conn.close()
        
    except Exception as e:
        logger.error(f"查询失败: {str(e)}")

if __name__ == "__main__":
    check_course_data() 